package cn.tuzirpa.executor;

import cn.tuzirpa.manager.DatabaseManager;
import cn.tuzirpa.tracker.SqlExecutionTracker;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Stream;

/**
 * SQL文件执行器
 * 负责读取指定目录下的SQL文件并执行
 */
public class SqlFileExecutor {
    
    private static final Logger logger = LoggerFactory.getLogger(SqlFileExecutor.class);
    
    private final DatabaseManager databaseManager;
    private final SqlExecutionTracker executionTracker;
    
    public SqlFileExecutor(DatabaseManager databaseManager, SqlExecutionTracker executionTracker) {
        this.databaseManager = databaseManager;
        this.executionTracker = executionTracker;
    }
    
    /**
     * 执行指定目录下的所有SQL文件
     */
    public void executeSqlFiles(String directoryPath) {
        Path directory = Paths.get(directoryPath);
        
        if (!Files.exists(directory) || !Files.isDirectory(directory)) {
            logger.error("目录不存在或不是有效目录: {}", directoryPath);
            throw new IllegalArgumentException("目录不存在或不是有效目录: " + directoryPath);
        }
        
        logger.info("开始执行目录 {} 下的SQL文件", directoryPath);
        
        try (Stream<Path> paths = Files.walk(directory)) {
            List<Path> sqlFiles = paths
                .filter(Files::isRegularFile)
                .filter(path -> path.toString().toLowerCase().endsWith(".sql"))
                .sorted() // 按文件名排序执行
                .collect(ArrayList::new, ArrayList::add, ArrayList::addAll);
            
            if (sqlFiles.isEmpty()) {
                logger.warn("目录 {} 下没有找到SQL文件", directoryPath);
                return;
            }
            
            logger.info("找到 {} 个SQL文件", sqlFiles.size());
            
            int successCount = 0;
            int skipCount = 0;
            int failureCount = 0;
            
            for (Path sqlFile : sqlFiles) {
                try {
                    boolean executed = executeSqlFile(sqlFile);
                    if (executed) {
                        successCount++;
                    } else {
                        skipCount++;
                    }
                } catch (Exception e) {
                    failureCount++;
                    logger.error("执行SQL文件失败: {}", sqlFile.getFileName(), e);
                }
            }
            
            logger.info("SQL文件执行完成 - 成功: {}, 跳过: {}, 失败: {}", 
                successCount, skipCount, failureCount);
            
        } catch (IOException e) {
            logger.error("遍历目录失败: {}", directoryPath, e);
            throw new RuntimeException("遍历目录失败", e);
        }
    }
    
    /**
     * 执行单个SQL文件
     */
    public boolean executeSqlFile(Path sqlFile) {
        String fileName = sqlFile.getFileName().toString();
        String filePath = sqlFile.toString();
        
        try {
            // 计算文件哈希值
            String fileHash = calculateFileHash(sqlFile);
            
            // 检查文件是否已执行过
            if (executionTracker.isFileExecuted(fileHash)) {
                logger.info("SQL文件 {} 已执行过，跳过", fileName);
                return false;
            }
            
            logger.info("开始执行SQL文件: {}", fileName);
            
            // 记录执行开始
            executionTracker.recordExecutionStart(fileName, filePath, fileHash);
            
            // 读取SQL文件内容并分割成语句
            List<String> sqlStatements = readSqlFile(sqlFile);
            
            try (Connection connection = databaseManager.getConnection()) {
                connection.setAutoCommit(false); // 开启事务
                
                try (Statement statement = connection.createStatement()) {
                    logger.info("执行SQL文件: {}", fileName);
                    logger.debug("SQL文件包含 {} 条语句", sqlStatements.size());
                    
                    // 添加所有SQL语句到批处理
                    for (int i = 0; i < sqlStatements.size(); i++) {
                        String sql = sqlStatements.get(i);
                        if (sql.trim().isEmpty()) {
                            continue;
                        }
                        
                        logger.debug("添加第 {} 条SQL到批处理: {}", i + 1, sql.substring(0, Math.min(100, sql.length())) + "...");
                        statement.addBatch(sql);
                    }
                    
                    // 执行批处理
                    logger.info("开始执行批处理，共 {} 条语句", sqlStatements.size());
                    int[] updateCounts = statement.executeBatch();
                    
                    // 记录执行结果
                    int totalAffectedRows = 0;
                    for (int i = 0; i < updateCounts.length; i++) {
                        int count = updateCounts[i];
                        if (count >= 0) {
                            totalAffectedRows += count;
                            logger.debug("第 {} 条SQL影响行数: {}", i + 1, count);
                        } else if (count == Statement.SUCCESS_NO_INFO) {
                            logger.debug("第 {} 条SQL执行成功，但影响行数未知", i + 1);
                        } else if (count == Statement.EXECUTE_FAILED) {
                            logger.warn("第 {} 条SQL执行失败", i + 1);
                        }
                    }
                    
                    connection.commit(); // 提交事务
                    executionTracker.recordExecutionSuccess(fileHash);
                    logger.info("SQL文件 {} 批处理执行成功，共执行 {} 条语句，总影响行数: {}", 
                               fileName, updateCounts.length, totalAffectedRows);
                    return true;
                    
                } catch (SQLException e) {
                    connection.rollback(); // 回滚事务
                    executionTracker.recordExecutionFailure(fileHash, e.getMessage());
                    logger.error("SQL文件 {} 批处理执行失败", fileName, e);
                    throw e;
                }
                
            } catch (SQLException e) {
                executionTracker.recordExecutionFailure(fileHash, e.getMessage());
                logger.error("SQL文件 {} 批处理执行失败", fileName, e);
                throw e;
            }
            
        } catch (Exception e) {
            logger.error("处理SQL文件失败: {}", fileName, e);
            throw new RuntimeException("处理SQL文件失败: " + fileName, e);
        }
    }
    
    /**
     * 读取SQL文件内容并分割成语句
     */
    private List<String> readSqlFile(Path sqlFile) throws IOException {
        List<String> sqlStatements = new ArrayList<>();
        StringBuilder currentStatement = new StringBuilder();
        
        try (BufferedReader reader = Files.newBufferedReader(sqlFile, StandardCharsets.UTF_8)) {
            String line;
            
            while ((line = reader.readLine()) != null) {
                // 保持原始行内容，让MySQL自己处理注释
                // 只跳过完全空白的行
                if (line.trim().isEmpty()) {
                    continue;
                }
                
                // 添加换行符，保持SQL格式
                currentStatement.append(line).append("\n");
                
                // 检查是否是完整的SQL语句
                if (line.trim().endsWith(";")) {
                    String sql = currentStatement.toString().trim();
                    if (!sql.isEmpty()) {
                        sqlStatements.add(sql);
                    }
                    currentStatement.setLength(0);
                }
            }
            
            // 处理最后一个没有分号结尾的语句
            String lastSql = currentStatement.toString().trim();
            if (!lastSql.isEmpty()) {
                sqlStatements.add(lastSql);
            }
        }
        
        logger.debug("从文件 {} 中读取到 {} 条SQL语句", sqlFile.getFileName(), sqlStatements.size());
        return sqlStatements;
    }
    
    
    
    /**
     * 计算文件哈希值
     */
    private String calculateFileHash(Path file) throws IOException, NoSuchAlgorithmException {
        MessageDigest digest = MessageDigest.getInstance("SHA-256");
        
        try (FileInputStream fis = new FileInputStream(file.toFile())) {
            byte[] buffer = new byte[8192];
            int bytesRead;
            while ((bytesRead = fis.read(buffer)) != -1) {
                digest.update(buffer, 0, bytesRead);
            }
        }
        
        byte[] hashBytes = digest.digest();
        StringBuilder hexString = new StringBuilder();
        
        for (byte b : hashBytes) {
            String hex = Integer.toHexString(0xff & b);
            if (hex.length() == 1) {
                hexString.append('0');
            }
            hexString.append(hex);
        }
        
        return hexString.toString();
    }
    
    /**
     * 执行指定的SQL语句
     */
    public void executeSql(String sql) throws SQLException {
        try (Connection connection = databaseManager.getConnection();
             Statement statement = connection.createStatement()) {
            
            logger.debug("执行SQL: {}", sql);
            statement.execute(sql);
            logger.info("SQL执行成功");
            
        } catch (SQLException e) {
            logger.error("SQL执行失败: {}", sql, e);
            throw e;
        }
    }
    
    /**
     * 批量执行SQL语句
     */
    public void executeSqlBatch(List<String> sqlStatements) throws SQLException {
        try (Connection connection = databaseManager.getConnection()) {
            connection.setAutoCommit(false);
            
            try (Statement statement = connection.createStatement()) {
                for (String sql : sqlStatements) {
                    if (sql.trim().isEmpty()) {
                        continue;
                    }
                    logger.debug("执行SQL: {}", sql.substring(0, Math.min(100, sql.length())) + "...");
                    statement.execute(sql);
                }
                
                connection.commit();
                logger.info("批量SQL执行成功，共 {} 条语句", sqlStatements.size());
                
            } catch (SQLException e) {
                connection.rollback();
                logger.error("批量SQL执行失败", e);
                throw e;
            }
        }
    }
}
